﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using Telerik.WebControls;
using EM.Repository.utility;
using System.Data.SqlClient;

namespace EM.reports.installment
{
    public partial class _default : System.Web.UI.Page
    {
        public string APPROOT = "";
        string[] array1;

        public string client_code = "";

        Conversion oC = new Conversion();

        private bool hasFULLAccess = false;
        private bool hasBMAccess = false;
        private bool hasLIMITEDAccess = false;

        protected void Page_Load(object sender, EventArgs e)
        {
            int PageId = 21;
            Authentication ua = new Authentication();
            ua.CheckRolePageAuthentication(PageId, Authentication.RedirectionURLTypes.LoginPage);

            hasFULLAccess = ua.HasPageFunctionAuthentication(21, "FULL");
            hasBMAccess = ua.HasPageFunctionAuthentication(21, "BM");
            hasLIMITEDAccess = ua.HasPageFunctionAuthentication(21, "LIMITED");

            AppPathFinder apf = new AppPathFinder();
            APPROOT = apf.GetApplicationPath();

            DbHelper srv = new DbHelper();

            if (!IsPostBack)
            {
                FillCombo fc = new FillCombo();
                fc.CreditCard(ddlCREDITCARD, 0, true, 0, Convert.ToInt32(Session["WORKGROUPID"]));


                if (hasFULLAccess)
                {
                    fc.User(ddlUSER, "0", true, Convert.ToInt32(Session["WORKGROUPID"]));
                    ddlUSER.Enabled = true;
                }
                else
                {
                    if (hasBMAccess)
                    {
                        fc.User(ddlUSER, "0", true, Convert.ToInt32(Session["BRANCHID"]));
                        ddlUSER.Enabled = true;
                    }
                    else
                    {
                        ddlUSER.Enabled = false;
                    }
                }
            }
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            rgMaster.Rebind();
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            rgMaster.ExportSettings.FileName = "rapor";
            rgMaster.ExportSettings.ExportOnlyData = true;
            rgMaster.ExportSettings.IgnorePaging = true;
            rgMaster.ExportSettings.OpenInNewWindow = true;
            rgMaster.MasterTableView.ExportToExcel();
        }

        private DataTable rgMaster_GetData()
        {
            DbHelper dsrv = new DbHelper();
            DataTable dt_return = new DataTable();
            SqlCommand cmd = new SqlCommand();
            string sSQL = "";
            sSQL += @" SELECT A.INSTALMENTDATE, SUM(AMOUNT)  FROM
                    (SELECT CONVERT(NVARCHAR(MAX), DATENAME(MM, I.INSTALMENTDATE) + ' ' + DATENAME(YY, I.INSTALMENTDATE)) AS INSTALMENTDATE, I.AMOUNT  FROM TBLEXPENSE E,TBLINSTALLMENT I
                    WHERE E.EXPENSEID = I.EXPENSEID AND I.WORKGROUPID = " + Session["WORKGROUPID"];

            if (!hasFULLAccess)
            {
                if (hasBMAccess)
                {
                    sSQL += " AND E.BRANCHID = @BRANCHID";
                    cmd.Parameters.AddWithValue("@BRANCHID", Session["BRANCHID"]);

                    if (ddlUSER.SelectedValue != "" && ddlUSER.SelectedValue != "0")
                    {
                        sSQL += " AND E.EXPENSEUSERID = @EXPENSEUSERID";
                        cmd.Parameters.AddWithValue("@EXPENSEUSERID", ddlUSER.SelectedValue);
                    }
                }
                else
                {
                    sSQL += " AND E.EXPENSEUSERID = @EXPENSEUSERID";
                    cmd.Parameters.AddWithValue("@EXPENSEUSERID", Session["USERID"]);
                }
            }
            else
            {
                if (ddlUSER.SelectedValue != "" && ddlUSER.SelectedValue != "0")
                {
                    sSQL += " AND E.EXPENSEUSERID = @EXPENSEUSERID";
                    cmd.Parameters.AddWithValue("@EXPENSEUSERID", ddlUSER.SelectedValue);
                }
            }

            if (rdpSTARTDATE.SelectedDate != rdpSTARTDATE.MinDate)
            {
                sSQL += " AND I.INSTALMENTDATE >= @STARTDATE ";
                cmd.Parameters.AddWithValue("@STARTDATE", rdpSTARTDATE.SelectedDate);
            }
            if (rdpENDDATE.SelectedDate != rdpENDDATE.MinDate)
            {
                sSQL += " AND I.INSTALMENTDATE <= @ENDDATE ";
                cmd.Parameters.AddWithValue("@ENDDATE", rdpENDDATE.SelectedDate);
            }

            if (ddlCREDITCARD.SelectedValue != "0" && ddlCREDITCARD.SelectedValue != "")
            {
                sSQL += " AND I.CREDITCARDID = @CREDITCARDID ";
                cmd.Parameters.AddWithValue("@CREDITCARDID", ddlCREDITCARD.SelectedValue);
            }

            sSQL += @"       ) AS A
                    GROUP BY A.INSTALMENTDATE
                    ORDER BY CONVERT(DATETIME, A.INSTALMENTDATE)
                    ";
            cmd.CommandText = sSQL;
            DataTable dt = dsrv.GetDataTable(cmd, "");
            if (dt.Rows.Count > 0)
            {
                cmd = new SqlCommand();
                sSQL = @" SELECT * FROM  
                            ( 
                            SELECT C.CREDITCARDNAME, INSTALMENTDATE, SUM(A.AMOUNT) AS AMOUNT,C.CREDITCARDID
                            FROM (  
	                                SELECT DATENAME(MM, I.INSTALMENTDATE) + ' ' + DATENAME(YY, I.INSTALMENTDATE) AS INSTALMENTDATE, I.AMOUNT, I.CREDITCARDID FROM TBLEXPENSE E,TBLINSTALLMENT I
			                        WHERE E.EXPENSEID = I.EXPENSEID AND I.WORKGROUPID = " + Session["WORKGROUPID"];

                if (!hasFULLAccess)
                {
                    if (hasBMAccess)
                    {
                        sSQL += " AND E.BRANCHID = @BRANCHID";
                        cmd.Parameters.AddWithValue("@BRANCHID", Session["BRANCHID"]);

                        if (ddlUSER.SelectedValue != "" && ddlUSER.SelectedValue != "0")
                        {
                            sSQL += " AND E.EXPENSEUSERID = @EXPENSEUSERID";
                            cmd.Parameters.AddWithValue("@EXPENSEUSERID", ddlUSER.SelectedValue);
                        }
                    }
                    else
                    {
                        sSQL += " AND E.EXPENSEUSERID = @EXPENSEUSERID";
                        cmd.Parameters.AddWithValue("@EXPENSEUSERID", Session["USERID"]);
                    }
                }
                else
                {
                    if (ddlUSER.SelectedValue != "" && ddlUSER.SelectedValue != "0")
                    {
                        sSQL += " AND E.EXPENSEUSERID = @EXPENSEUSERID";
                        cmd.Parameters.AddWithValue("@EXPENSEUSERID", ddlUSER.SelectedValue);
                    }
                }
                if (rdpSTARTDATE.SelectedDate != rdpSTARTDATE.MinDate)
                {
                    sSQL += " AND I.INSTALMENTDATE >= @STARTDATE ";
                    cmd.Parameters.AddWithValue("@STARTDATE", rdpSTARTDATE.SelectedDate);
                }
                if (rdpENDDATE.SelectedDate != rdpENDDATE.MinDate)
                {
                    sSQL += " AND I.INSTALMENTDATE <= @ENDDATE ";
                    cmd.Parameters.AddWithValue("@ENDDATE", rdpENDDATE.SelectedDate);
                }
                if (ddlCREDITCARD.SelectedValue != "0" && ddlCREDITCARD.SelectedValue != "")
                {
                    sSQL += " AND I.CREDITCARDID = @CREDITCARDID ";
                    cmd.Parameters.AddWithValue("@CREDITCARDID", ddlCREDITCARD.SelectedValue);
                }



                sSQL += @" )A, TBLCREDITCARD C  
                         	                        WHERE A.CREDITCARDID=C.CREDITCARDID 
                                    GROUP BY C.CREDITCARDNAME, C.CREDITCARDID, INSTALMENTDATE
                        )a 
                        PIVOT  
                        ( 
                        SUM(AMOUNT) 
                        FOR INSTALMENTDATE IN  
                        ( ";

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i == dt.Rows.Count - 1)//Son satırsa virgül koyma
                    {
                        sSQL += "[" + dt.Rows[i][0].ToString() + "]";
                    }
                    else
                    {
                        sSQL += "[" + dt.Rows[i][0].ToString() + "],";
                    }
                }

                sSQL += @" 	) ) AS PIVOTTABLE ";
                cmd.CommandText = sSQL;

                dt_return = dsrv.GetDataTable(cmd, "");

            }

            dt_return.Columns.Add(new DataColumn("Toplam"));

            return dt_return;
        }

        protected void rgMaster_NeedDataSource(object source, Telerik.WebControls.GridNeedDataSourceEventArgs e)
        {
            if (Page.IsPostBack)
            {
                DataTable dt = rgMaster_GetData();
                rgMaster.DataSource = dt;

                rgMaster.MasterTableView.NoMasterRecordsText = "Kayıt bulunamadı.";
                rgMaster.StatusBarSettings.ReadyText = dt.Rows.Count + " adet kayıt bulundu.";

                rgMaster.Width = dt.Columns.Count * 20 + 200;
            }
        }

        protected void rgMaster_ColumnCreated(object sender, GridColumnCreatedEventArgs e)
        {
            if (e.Column.HeaderText == "CREDITCARDID")
            {
                e.Column.Visible = false;
            }
        }

        protected void rgMaster_ItemDataBound(object sender, Telerik.WebControls.GridItemEventArgs e)
        {

            if (e.Item.ItemType == Telerik.WebControls.GridItemType.Header)
            {
                array1 = new string[e.Item.Cells.Count];

                for (int i = 4; i < e.Item.Cells.Count; i++)
                {
                    if (e.Item.Cells[i].Text != "Toplam" && e.Item.Cells[i].Text != "CREDITCARDID")
                    {
                        array1[i] = e.Item.Cells[i].Text;
                    }
                    e.Item.Cells[i].Attributes["style"] = "writing-mode: tb-rl; filter: flipv flipv;width:10px;";
                }
                e.Item.Cells[2].Text = "Kredi Kartı";
                e.Item.Cells[2].Width = System.Web.UI.WebControls.Unit.Pixel(150);
            }

            if (e.Item.ItemType == Telerik.WebControls.GridItemType.Item || e.Item.ItemType == Telerik.WebControls.GridItemType.AlternatingItem)
            {
                double amount = 0;
                DataRowView drv = (DataRowView)e.Item.DataItem;

                for (int i = 4; i < e.Item.Cells.Count - 1; i++)
                {
                    if (e.Item.Cells[i].Text == "&nbsp;")
                    {
                        e.Item.Cells[i].Text = "0";
                    }
                    else
                    {
                        amount += Convert.ToDouble(e.Item.Cells[i].Text);
                        e.Item.Cells[i].Text = oC.CNumS2C(Convert.ToDouble(e.Item.Cells[i].Text));
                    }
                }


                e.Item.Cells[e.Item.Cells.Count - 1].Text = oC.CNumS2C(amount);
                e.Item.Cells[e.Item.Cells.Count - 1].Width = System.Web.UI.WebControls.Unit.Pixel(10);

            }
            if (e.Item.ItemType == Telerik.WebControls.GridItemType.Footer)
            {

                double roomcount = 0;

                DataTable dt = (DataTable)((Telerik.WebControls.RadGrid)e.Item.Parent.Parent.Parent.Parent).DataSource;

                //sutun bazinda toplamlar
                if (dt.Rows.Count > 0)
                {
                    for (int column = 4; column < rgMaster.Items[0].Cells.Count; column++)
                    {
                        for (int row = 0; row < rgMaster.Items.Count; row++)
                        {
                            roomcount += Convert.ToDouble(rgMaster.Items[row][dt.Columns[column - 2].ColumnName].Text);
                        }
                        e.Item.Cells[column].Text = "<b>" + roomcount.ToString() + "</b>";
                        e.Item.Cells[column].Style.Add("text-align", "right");
                        roomcount = 0;
                    }
                }

                e.Item.Cells[2].Text = "<b>Toplam</b>";
            }

        }

    }
}